<?php
require_once '../library/PHPExcel.php';  
class ExportController extends ZendX_Controller_Action
{
	public function testAction()
	{
		$excel = new PHPExcel();
		
		$a = array(
			array('name'=>'aa', 'age'=>1, 'likes'=>array('aa', 'bb')),
			array('name'=>'bb', 'age'=>2,'likes'=>array('bb','cc','dd')),
			array('name'=>'aa', 'age'=>3, 'likes'=>array('aa', 'bb')),
		);
		
//		$b = array(
//			array('name'=>'钢筋', 'code'=>'01', 'props'=>array(
//				array('name'=>'直径', 'value'=>'10'),
//				array('name'=>'品种', 'value'=>'螺纹钢')
//			)),
//			
//			array('name'=>'塑料', 'code'=>'01', 'props'=>array(
//				array('name'=>'压强', 'value'=>'10'),
//				array('name'=>'密度', 'value'=>'aa')
//			)),
//		);
		
		$this->writeExcel($excel, $a);
		
		$this->exportExcel($excel, 'test');		
		
		exit();
	}
	
	public function askAction($begin, $end)
	{
		$filename = $begin . "--" . $end;
		$begin = strtotime($begin);
    	$end = strtotime($end);
    		
		$excel = new PHPExcel();
		
		$ask = $this->db('askplat')->fetchAll("select distinct a.user_company, a.c,b.ask_id,b.item_content from
					(select a.u_id,a.user_company,b.c from passport.dbo.t_passport_user a right join
					(select u_id,count(ask_id) as c from dbo.t_askplat_asklist
					where is_deleted=0 and dateline between '$begin' and '$end'
					group by u_id) as b on a.u_id=b.u_id) as a left join
					(select a.u_id,b.ask_id,b.item_content from
					(select ask_id,u_id from dbo.t_askplat_asklist
					where is_deleted=0 and dateline between '$begin' and '$end') as a left join t_askplat_item b
					on a.ask_id = b.ask_id) as b on a.u_id=b.u_id
					where b.item_content is not null
					order by a.c desc");
		
		$company = array();
		$askid = array();
		foreach ($ask as $i=>$r)
		{
			if (in_array($r['user_company'], $company)) {
				$ask[$i]['user_company'] = '';
				$ask[$i]['c'] = '';
			}
			else 
			{
				$company[] = $r['user_company'];
			}
			
			if (in_array($r['ask_id'], $askid)) {
				$ask[$i]['ask_id'] = '';				
			}
			else 
			{
				$askid[] = $r['ask_id'];
			}
			
		}
		
		$this->writeExcel($excel, $ask, '询价', array('user_company'=>'公司名称', 'c'=>'询价次数', 'ask_id'=>'询价单号', 'item_content'=>'询价内容'), 0);
		
		$ms = $this->db('askplat')->fetchAll("select [name],count(reply_item_id) as 'count1'  ,count(distinct ask_id) as 'count2'  from dbo.t_askplat_reply_item where ask_id in (
				select ask_id from dbo.t_askplat_asklist where is_deleted=0 and dateline between '$begin' and '$end')group by [name] order by 2 desc");
		
		$this->writeExcel($excel, $ms, '材料', array('name'=>'材料名称', 'count1'=>'询价条数', 'count2'=>'询价次数'), 1);
		
		
		$brand = $this->db('askplat')->fetchAll("select brand,count(reply_item_id) as 'itemcount' ,count(distinct ask_id) as 'askcount'   from dbo.t_askplat_reply_item where ask_id in (
					select ask_id from dbo.t_askplat_asklist where is_deleted=0 and dateline between '$begin' and '$end')group by brand order by 2 desc");
		
		$this->writeExcel($excel, $brand, '品牌', array('brand'=>'品牌', 'itemcount'=>'询价条数', 'askcount'=>'询价次数'), 2);
		
		$this->exportExcel($excel, $filename);		
		
		exit();
	}
	
	public function askcompanyAction($company)
	{
		$excel = new PHPExcel();
		$cs = $this->db('askplat')->fetchAll("select a.ask_id, a.dateline, b.bt, b.et,
			a.contactor, ac.reply_speed, ac.reply_qua, ac.comment_content,
			ai.item_content
			from dbo.t_askplat_asklist a, dbo.t_askplat_asklist_comment ac, dbo.t_askplat_item ai,
			(select ar.ask_id, min(ar.reply_dateline) as bt, max(ar.reply_dateline) as et 
			from dbo.t_askplat_reply ar
			group by ar.ask_id
			) b
			where a.ask_id = ac.ask_id
			and a.ask_id = ai.ask_id
			and a.ask_id = b.ask_id
			and a.company = ?", $company);
		
		$asks = array();
		$reply_speed = 0;
		$reply_qua = 0;
		foreach ($cs as $k=>$v)
		{
			$cs[$k]['dateline']=date('Y-m-d H:m:s', $v['dateline']);
			$cs[$k]['bt']=date('Y-m-d H:m:s', $v['bt']);
			$cs[$k]['et']=date('Y-m-d H:m:s', $v['et']);
			
			if (in_array($v["ask_id"], $asks)) {
				$cs[$k]['ask_id']='';
				$cs[$k]['dateline']='';
				$cs[$k]['bt']='';
				$cs[$k]['et']='';
				$cs[$k]['contactor']='';
				$cs[$k]['reply_speed']='';
				$cs[$k]['reply_qua']='';
				$cs[$k]['comment_content']='';
			}
			else 
			{
				$asks[] = $v['ask_id'];
				$reply_speed += $v['reply_speed'];
				$reply_qua += $v['reply_qua'];
			}
		}
		
		$cs[] = array(
			'ask_id'=>'',
			'dateline'=>'',
			'bt'=>'',
			'et'=>'',
			'contactor'=>'平均',
			'reply_speed'=>$reply_speed/count($asks),
			'reply_qua'=>$reply_qua/count($asks),
			'comment_content'=>'',
			'item_content'=>'',
			'item_content'=>'',
		);
		
		$titles = array(
			'ask_id'=>'询价单号',
			'dateline'=>'询价时间',
			'bt'=>'第一次回复时间',
			'et'=>'最后回复时间',
			'contactor'=>'联系人',
			'reply_speed'=>'回复速度',
			'reply_qua'=>'回复内容',
			'comment_content'=>'评价内容',
			'item_content'=>'询价内容'
		);
		$this->writeExcel($excel, $cs, '有评分评论', $titles, 0);
		
		$cs = $this->db('askplat')->fetchAll("select a.ask_id, a.dateline, b.bt, b.et,
			a.contactor, 
			ai.item_content
			from dbo.t_askplat_asklist a, dbo.t_askplat_item ai,
			(select ar.ask_id, min(ar.reply_dateline) as bt, max(ar.reply_dateline) as et 
			from dbo.t_askplat_reply ar
			group by ar.ask_id
			) b
			where a.ask_id = ai.ask_id
			and a.ask_id = b.ask_id	
			and a.ask_id not in (select ask_id from dbo.t_askplat_asklist_comment) 		
			and a.company = ?", $company);
		
		$asks = array();
		foreach ($cs as $k=>$v)
		{
			$cs[$k]['dateline']=date('Y-m-d H:m:s', $v['dateline']);
			$cs[$k]['bt']=date('Y-m-d H:m:s', $v['bt']);
			$cs[$k]['et']=date('Y-m-d H:m:s', $v['et']);
			
			if (in_array($v["ask_id"], $asks)) {
				$cs[$k]['ask_id']='';
				$cs[$k]['dateline']='';
				$cs[$k]['bt']='';
				$cs[$k]['et']='';
				$cs[$k]['contactor']='';				
			}
			else 
			{
				$asks[] = $v['ask_id'];
			}
		}
		
		$this->writeExcel($excel, $cs, '无评分评论', $titles, 1);
		
		$this->exportExcel($excel, $company);		
		
		exit();
	}
}